﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using Esint.Common.Data;
using Esint.JudicialAuction.Model;
using Esint.JudicialAuction.IDAL;
using Esint.Common.Model;
using System.Data.SqlClient;

namespace Esint.JudicialAuction.SqlDAL
{
    /// <summary>
    /// 模块名称： 数据访问层
    /// 作    者：谷明
    /// 生成日期：2020年03月29日
    /// 生成模板: Esint.Template.SqlDAL.SqlDAL_11 版
    ///           适用于工厂模式架构
    /// 修改说明：
    /// </summary>
    public partial class Schedule_UserData : BaseData, ISchedule_UserData
    {
        /// <summary>
        /// 获得人员比
        /// </summary>
        /// <returns></returns>
        public double[] GetAvailableUserGenderNum()
        {
            DataQuery query = new DataQuery();
            query.SQLText = "select Gender,count(1) NCount from T_Schedule_User where IsAvailable=1 group by Gender";
            var dt = ExecuteDataTable(query);
            double[] res = new double[3];
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DataRow dr = dt.Rows[i];
                if (dr[0].ToString() == "1")
                {
                    res[0] = Convert.ToDouble(dr[1]);
                }
                else
                {
                    res[1] = Convert.ToDouble(dr[1]);
                }
            }

            double total = res[0] + res[1];
            res[2] = total;

            return res;
        }

        /// <summary>
        /// 获得当前排班值不是最大的，并且符合不连班需求的所有人方法（用于顺序排班的第二天排白/夜班）
        /// </summary>
        /// <param name="currDate">要排班的日期</param>
        /// <param name="workType">值班类型</param>
        /// <returns></returns>
        public List<Schedule_UserInfo> GetCanBeAutoAndMinestTimesList(string currDate, int workType)
        {
            DataQuery query = new DataQuery();
            
            var currentDate = Convert.ToDateTime(currDate);
            var previewDate = currentDate.AddDays(-1);
            var tomorrowDate = currentDate.AddDays(1);

            var preWorkTypeWhere = string.Empty;
            if (workType == 2)
            {
                preWorkTypeWhere = " and ts.WorkTypeCode=2 ";
            }
            var nextWorkTypeWhere = string.Empty;
            if (workType == 1)
            {
                nextWorkTypeWhere = " and ts.WorkTypeCode=1 ";
            }

            query.SQLText = @"select *
, (select c.meaning from T_Sys_Code c where c.flag=3 and c.code=IsAvailable) IsAvailableMeaning
, (select c.meaning from T_Sys_Code c where c.flag=4 and c.code=Gender) GenderMeaning
from T_Schedule_User 
where IsDelete=0 and UserId not in 
    (select ts.UserId from T_Schedule_Shifts ts where (ts.WorkDate=@preWorkDate " + preWorkTypeWhere + @") or (ts.WorkDate=@WorkDate) or (ts.WorkDate=@nextWorkDate "+ nextWorkTypeWhere + @")  ) 
    and IsAvailable=1
order by ScheduleWeight";
            query.WhereParameters.Add(new WhereParameter("@preWorkDate", previewDate.ToString("yyyy-MM-dd")));
            query.WhereParameters.Add(new WhereParameter("@WorkDate", currDate));
            query.WhereParameters.Add(new WhereParameter("@nextWorkDate", tomorrowDate.ToString("yyyy-MM-dd")));
            var list = GetModelList<Schedule_UserInfo>(query);
            return list;
        }

        /// <summary>
        /// 根据查询条件获得所有排班人员分页集合对象
        /// </summary>
        /// <param name="userName"></param>
        /// <param name="isAvailable"></param>
        /// <returns></returns>
        public PagedList<Schedule_UserInfo> GetSchedulUserList(string userName, byte? isAvailable,int? gender, int pageIndex, int pageSize)
        {
            DataQuery query = new DataQuery();
            query.SQLText = @"select *
, (select c.meaning from T_Sys_Code c where c.flag=3 and c.code=IsAvailable) IsAvailableMeaning 
, (select c.meaning from T_Sys_Code c where c.flag=4 and c.code=Gender) GenderMeaning
from T_Schedule_User where IsDelete=0 ";

            if (!string.IsNullOrWhiteSpace(userName))
            {
                query.SQLText += " and UserName like @UserName ";
                query.WhereParameters.Add(new WhereParameter("@UserName", $"%{userName}%"));
            }
            if (isAvailable != null)
            {
                query.SQLText += " and IsAvailable = @IsAvailable ";
                query.WhereParameters.Add(new WhereParameter("@IsAvailable", isAvailable.Value));
            }
            if (gender != null)
            {
                query.SQLText += " and Gender = @Gender ";
                query.WhereParameters.Add(new WhereParameter("@Gender", gender.Value));
            }

            query.OrderByString = " UserName ";

            var pagedList = new PagedList<Schedule_UserInfo>();
            var split = new PageSplit() { IsPageView = true, PageIndex = pageIndex, PageSize = pageSize };
            query.PageView = split;

            var resultList = GetModelList<Schedule_UserInfo>(query);
            pagedList.DataList = resultList;
            pagedList.PageInfo = query.PageView;
            return pagedList;
        }
        
        /// <summary>
        /// 保存列表方法
        /// </summary>
        /// <param name="list"></param>
        public void SaveMutli(List<Schedule_UserInfo> list)
        {
            IDbTransaction trans = basicDataAccess.GetTransaction();

            try
            {
                for (int i = 0; i < list.Count; i++)
                {
                    var item = list[i];

                    Insert(item, trans);
                }

                trans.Commit();
            }
            catch (Exception ex)
            {
                trans.Rollback();
                throw ex;
            }
        }

        /// <summary>
        /// 获得所有用户列表
        /// </summary>
        /// <returns></returns>
        public List<Schedule_UserInfo> GetAllUserList()
        {
            DataQuery query = new DataQuery();
            query.SQLText = "select * from T_Schedule_User where IsDelete=0 ";
            return GetModelList<Schedule_UserInfo>(query);
        }

        /// <summary>
        /// 根据id集合获得用户列表
        /// </summary>
        /// <param name="userIdList"></param>
        /// <returns></returns>
        public List<Schedule_UserInfo> GetUserListByIdList(List<string> userIdList)
        {
            DataQuery query = new DataQuery();

            string strWhere = GetListSqlAndParams(userIdList, query.WhereParameters);

            query.SQLText = $"select * from T_Schedule_User where UserId in {strWhere}";

            return GetModelList<Schedule_UserInfo>(query);
        }

        /// <summary>
        /// 判断系统是否进行了初始化
        /// </summary>
        /// <returns></returns>
        public bool IsSystemInit()
        {
            DataQuery query = new DataQuery();
            query.SQLText = @"select count(t1.UserId) pcount from T_Schedule_User t1 where t1.Priority=0
union all select count(t2.UserId) allcount from T_Schedule_User t2";

            var dt = ExecuteDataTable(query);
            if (dt.Rows[0][0].ToString() == dt.Rows[1][0].ToString())
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        /// <summary>
        /// 获得所有参与排班的用户的权重平均值
        /// </summary>
        /// <returns></returns>
        public int GetAllUserAverageWeight()
        {
            DataQuery query = new DataQuery();
            query.SQLText = "select ISNULL( AVG(u.ScheduleWeight),0) averageWeight from T_Schedule_User u where u.IsDelete=0 and u.IsAvailable=1 ";

            var dt = ExecuteDataTable(query);
            double weight = Convert.ToDouble(dt.Rows[0][0]);
            return (int)Math.Ceiling(weight);
        }
    }
}
